#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库连接和初始化模块
"""
import sqlite3
import os
import datetime
from ..utils.config import config
from ..utils.security import hash_password


def get_current_time():
    """获取当前时间（上海时区）"""
    try:
        # 尝试使用系统时区设置
        return datetime.datetime.now().astimezone().strftime('%Y-%m-%d %H:%M:%S')
    except ValueError:
        # 如果系统时区不可用，使用上海时区
        try:
            from zoneinfo import ZoneInfo
            shanghai_tz = ZoneInfo('Asia/Shanghai')
            return datetime.datetime.now(shanghai_tz).strftime('%Y-%m-%d %H:%M:%S')
        except ImportError:
            try:
                import pytz
                shanghai_tz = pytz.timezone('Asia/Shanghai')
                return datetime.datetime.now(shanghai_tz).strftime('%Y-%m-%d %H:%M:%S')
            except ImportError:
                # 如果都没有，使用默认时区
                return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


def get_db_connection():
    """获取数据库连接"""
    conn = sqlite3.connect('data/database.db')
    conn.row_factory = sqlite3.Row
    return conn


def init_database():
    """初始化数据库"""
    os.makedirs('data', exist_ok=True)
    conn = sqlite3.connect('data/database.db')
    cursor = conn.cursor()
    
    # 用户表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            phone TEXT,
            email TEXT,
            role TEXT DEFAULT 'user',
            is_active BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            updated_at TIMESTAMP DEFAULT (datetime('now', 'localtime'))
        )
    ''')
    
    # 密码表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS passwords (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            url TEXT NOT NULL,
            account TEXT NOT NULL,
            password TEXT NOT NULL,
            description TEXT,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            updated_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    ''')
    
    # 密码历史表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS password_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            password_id INTEGER NOT NULL,
            old_password TEXT NOT NULL,
            changed_by INTEGER NOT NULL,
            changed_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (password_id) REFERENCES passwords (id) ON DELETE CASCADE,
            FOREIGN KEY (changed_by) REFERENCES users (id)
        )
    ''')
    
    # 用户会话表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS user_sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            session_token TEXT UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            expires_at TIMESTAMP NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    ''')
    
    # 密码分享表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS password_shares (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            password_id INTEGER NOT NULL,
            share_token TEXT UNIQUE NOT NULL,
            access_password TEXT,
            expires_at TIMESTAMP,
            access_count INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (password_id) REFERENCES passwords (id) ON DELETE CASCADE
        )
    ''')
    
    # 访问日志表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS access_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            share_token TEXT NOT NULL,
            ip_address TEXT NOT NULL,
            user_agent TEXT,
            accessed_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (share_token) REFERENCES password_shares (share_token) ON DELETE CASCADE
        )
    ''')
    
    # 用户活动日志表
    cursor.execute('''
''')
    
    # 密码重置表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS password_resets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            token TEXT UNIQUE NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    ''')
    
    # 邮箱验证码表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS email_verifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            code TEXT NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime'))
        )
    ''')
    
    # 日历事件表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS calendar_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            event_time TIMESTAMP NOT NULL,
            is_reminder BOOLEAN DEFAULT 0,
            reminder_time TIMESTAMP,
            reminder_sent BOOLEAN DEFAULT 0,
            created_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            updated_at TIMESTAMP DEFAULT (datetime('now', 'localtime')),
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    ''')
    
    # 检查是否需要创建默认管理员账户
    admin_user = conn.execute("SELECT id FROM users WHERE username = 'admin'").fetchone()
    if not admin_user:
        admin_password = config.get('DEFAULT', 'admin_password')
        admin_hash = hash_password(admin_password)
        cursor.execute("""
            INSERT INTO users (username, password_hash, email, role, is_active)
            VALUES (?, ?, ?, 'admin', 1)
        """, ('admin', admin_hash, 'gza5210@163.com'))
    
    conn.commit()
    conn.close()